/**
 * DishDAO.java
 * Nov 7, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import XMLEntities.DishXM;

import DBUltilities.ConnectDB;
import DTO.CookingDTO;
import DTO.DishDTO;
import DTO.FoodDTO;

/**
 * @author trandophong
 * @version 1.0
 */
public class DishDAO {
	public DishXM convertDishtoDishXM(DishDTO dishDTO) {
		DishXM dishXM = new DishXM();
		dishXM.setId(dishDTO.getId());
		dishXM.setCalo(dishDTO.getCalo());
		CookingDAO cookingDAO = new CookingDAO();
		cookingDAO.get(dishDTO.getCooking());
		dishXM.setCookingName(dishDTO.getCooking().getName());
		dishXM.setDescription(dishDTO.getDescription());
		dishXM.setImgPath(dishDTO.getImgPath());
		dishXM.setName(dishDTO.getName());
		
		return dishXM;
	}
	
	public void get(DishDTO dish) {
		if (dish.getId() != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;

			String sqlStatement = "SELECT * FROM DISH WHERE ID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, dish.getId());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					dish.setId((Integer) rs.getObject(1));
					dish.setName(new String(rs.getObject(2) == null ? "" : rs
							.getObject(2).toString()));
					dish.setCalo(Integer.parseInt(rs.getObject(3) == null ? "0"
							: rs.getObject(3).toString()));

					CookingDTO cooking = new CookingDTO();
					cooking.setId(Integer.parseInt(rs.getObject(4) == null ? "0"
							: rs.getObject(4).toString()));
					dish.setCooking(cooking);

					dish.setDescription(new String(rs.getObject(5) == null ? ""
							: rs.getObject(5).toString()));
					dish.setImgPath(new String(rs.getObject(6) == null ? ""
							: rs.getObject(6).toString()));
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public void add(DishDTO dish) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO DISH (NAME, CALO, COOKINGID, DESCRIPTION, IMGPATH) VALUES"
				+ "(?, ?, ?, ?, ?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement, Statement.RETURN_GENERATED_KEYS);

			preparedStatement.setString(1, dish.getName());
			preparedStatement.setInt(2, dish.getCalo());
			preparedStatement.setInt(3, dish.getCooking().getId());
			preparedStatement.setString(4, dish.getDescription());
			preparedStatement.setString(5, dish.getImgPath());

			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				dish.setId(rs.getInt(1));
			}
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void update(DishDTO dish) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE DISH SET NAME = ?, COOKINGID = ?, DESCRIPTION = ?, IMGPATH = ? WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, dish.getName());
			preparedStatement.setInt(2, dish.getCooking().getId());
			preparedStatement.setInt(3, dish.getCalo());
			preparedStatement.setString(4, dish.getDescription());
			preparedStatement.setString(5, dish.getImgPath());
			preparedStatement.setInt(6, dish.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM DISH WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, id);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public ArrayList<DishDTO> select() {
		Connection conn = null;
		Statement statement = null;
		ArrayList<DishDTO> list = new ArrayList<DishDTO>();

		String sqlStatement = "SELECT * FROM DISH";
		try {
			conn = ConnectDB.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlStatement);

			int id;
			String name;
			int calo;
			int cookingId;
			CookingDTO cooking;
			String description;
			String imgPath;

			while (rs.next()) {
				id = (Integer) rs.getObject(1);
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());
				calo = Integer.parseInt(rs.getObject(3) == null ? "0" : rs
						.getObject(3).toString());
				cookingId = Integer.parseInt(rs.getObject(4) == null ? "0" : rs
						.getObject(4).toString());
				cooking = new CookingDTO();
				cooking.setId(cookingId);
				description = new String(rs.getObject(5) == null ? "" : rs
						.getObject(5).toString());
				imgPath = new String(rs.getObject(6) == null ? "" : rs
						.getObject(6).toString());

				DishDTO dishItem = new DishDTO(id, name, calo, cooking,
						description, imgPath);
				list.add(dishItem);
			}
		} catch (Exception e) {
			return null;
		}
		return list;
	}
	
	public ArrayList<DishDTO> getDishInRecipe (int recipeId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ArrayList<DishDTO> list = new ArrayList<DishDTO>();

		String sqlStatement = "SELECT * FROM RECIPE_DISH WHERE RECIPEID = ?";
		try {
			conn = ConnectDB.getConnection();
			
			preparedStatement = conn.prepareStatement(sqlStatement);
			preparedStatement.setInt(1, recipeId);
			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int dishId = Integer.parseInt(rs.getObject(2) == null ? "0" : rs
						.getObject(2).toString());
				DishDTO dishDTO = new DishDTO();
				dishDTO.setId(dishId);
				get(dishDTO);
				list.add(dishDTO);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}
	
	public void getFoodsInDish (DishDTO dishDTO) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "SELECT * FROM DISH_FOOD WHERE DISHID=?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, dishDTO.getId());
			ResultSet rs = preparedStatement.executeQuery();

			int foodId;
			
			while (rs.next()) {
				foodId = Integer.parseInt(rs.getObject(2) == null ? "0" : rs
						.getObject(2).toString());

				FoodDTO foodDTO = new FoodDTO();
				foodDTO.setId(foodId);
				dishDTO.getFoodList().add(foodDTO);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void addDishFood(ArrayList<Integer> foodList, int dishId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		conn = ConnectDB.getConnection();
		
		// delete Food in Dish 
		String sqlStatement = "DELETE FROM DISH_FOOD WHERE DISHID=?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, dishId);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		// add new Food into Dish
		sqlStatement = "INSERT INTO DISH_FOOD (DISHID, FOODID) VALUES"
				+ "(?, ?)";

		try {
			preparedStatement = conn.prepareStatement(sqlStatement);
			conn.setAutoCommit(false);

			for (Integer foodId : foodList) {
				preparedStatement.setInt(1, dishId);
				preparedStatement.setInt(2, foodId);
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();

			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}
}
